package persistencia;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import modeloDatos.Curso;
import modeloDatos.CursosVistaReducida;
import modeloDatos.GrupoVistaReducida;

public class DAOCursos {

	private Connection conn;
	
	public DAOCursos(){
	}
    	
	public void finalize(){
		ConnectionManager.getInstance().checkIn(conn);
    }
	
	public List<CursosVistaReducida> recuperaTodosCursos(){
		conn = ConnectionManager.getInstance().checkOut();
		
        ResultSet result = null;
        List<CursosVistaReducida> cursos = new LinkedList<CursosVistaReducida>();
        PreparedStatement stmt = null;
        String sql = "SELECT * FROM cursos";
        try{
        	stmt = conn.prepareStatement(sql);
        	result = stmt.executeQuery();
        	while(result.next()){
        		CursosVistaReducida cursoAux = new CursosVistaReducida(result.getString("idCurso"), result.getString("descripcion"), result.getString("especialidad"), result.getString("entidad"));
        		cursos.add(cursoAux);
        	}
        }catch(SQLException ex){
        	System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
        }finally{
        	finalize();
        	try{
        		if(result != null){
        			result.close();
        		}
        		if(stmt!= null){
        			stmt.close();
        		}
        	}catch(SQLException ex){
        	}
        } 
        return cursos;
	}
	
	public List<String> recuperaTiposCurso(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT descripcion FROM tiposcursos";
        ResultSet result = null;
        List<String> tiposCursos = new LinkedList<String>();
        
        try{
        	stmt = conn.prepareStatement(sql);
        	result = stmt.executeQuery();
        	while(result.next()){
        		tiposCursos.add(result.getString("descripcion"));
        	}
        }catch(SQLException ex){
        	System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
        }finally{
        	finalize();
        	try{
        		if(result != null){
        			result.close();
        		}
        		if(stmt != null){
        			stmt.close();
        		}
        	}catch(SQLException ex){
        	}
        } 
        return tiposCursos;
	}
	
	public List<String> recuperaRequisitosCurso(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT descripcion FROM requisitoscursos";
        ResultSet result = null;
        List<String> tiposCursos = new LinkedList<String>();
        
        try{
        	stmt = conn.prepareStatement(sql);
        	result = stmt.executeQuery();
        	while(result.next()){
        		tiposCursos.add(result.getString("descripcion"));
        	}
        }catch(SQLException ex){
        	System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
        }finally{
        	finalize();
        	try{
        		if(result != null){
        			result.close();
        		}
        		if(stmt != null){
        			stmt.close();
        		}
        	}catch(SQLException ex){
        	}
        } 
        return tiposCursos;
	}
	
	public List<String> recuperaSituacionCurso(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT descripcion FROM situacionescurso";
        ResultSet result = null;
        List<String> tiposCursos = new LinkedList<String>();
        
        try{
        	stmt = conn.prepareStatement(sql);
        	result = stmt.executeQuery();
        	while(result.next()){
        		tiposCursos.add(result.getString("descripcion"));
        	}
        }catch(SQLException ex){
        	System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
        }finally{
        	finalize();
        	try{
        		if(result != null){
        			result.close();
        		}
        		if(stmt != null){
        			stmt.close();
        		}
        	}catch(SQLException ex){
        	}
        } 
        return tiposCursos;
	}
	
	public List<String> recuperaMetodoDocenteCurso(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT descripcion FROM metodosdocentes";
        ResultSet result = null;
        List<String> tiposCursos = new LinkedList<String>();
        
        try{
        	stmt = conn.prepareStatement(sql);
        	result = stmt.executeQuery();
        	while(result.next()){
        		tiposCursos.add(result.getString("descripcion"));
        	}
        }catch(SQLException ex){
        	System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
        }finally{
        	finalize();
        	try{
        		if(result != null){
        			result.close();
        		}
        		if(stmt != null){
        			stmt.close();
        		}
        	}catch(SQLException ex){
        	}
        } 
        return tiposCursos;
	}
	
	public List<String> recuperaPlataformaCurso(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT descripcion FROM plataformascursos";
        ResultSet result = null;
        List<String> tiposCursos = new LinkedList<String>();
        
        try{
        	stmt = conn.prepareStatement(sql);
        	result = stmt.executeQuery();
        	while(result.next()){
        		tiposCursos.add(result.getString("descripcion"));
        	}
        }catch(SQLException ex){
        	System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
        }finally{
        	finalize();
        	try{
        		if(result != null){
        			result.close();
        		}
        		if(stmt != null){
        			stmt.close();
        		}
        	}catch(SQLException ex){
        	}
        } 
        return tiposCursos;
	}
	
	public List<String> recuperaEntidadesCurso(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT DISTINCT entidad FROM cursos";
        ResultSet result = null;
        List<String> tiposCursos = new LinkedList<String>();
        
        try{
        	stmt = conn.prepareStatement(sql);
        	result = stmt.executeQuery();
        	while(result.next()){
        		tiposCursos.add(result.getString("entidad"));
        	}
        }catch(SQLException ex){
        	System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
        }finally{
        	finalize();
        	try{
        		if(result != null){
        			result.close();
        		}
        		if(stmt != null){
        			stmt.close();
        		}
        	}catch(SQLException ex){
        	}
        } 
        return tiposCursos;
	}
	
	public List<String> recuperaEspecialidadesCurso(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT DISTINCT especialidad FROM cursos";
        ResultSet result = null;
        List<String> tiposCursos = new LinkedList<String>();
        
        try{
        	stmt = conn.prepareStatement(sql);
        	result = stmt.executeQuery();
        	while(result.next()){
        		tiposCursos.add(result.getString("especialidad"));
        	}
        }catch(SQLException ex){
        	System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
        }finally{
        	finalize();
        	try{
        		if(result != null){
        			result.close();
        		}
        		if(stmt != null){
        			stmt.close();
        		}
        	}catch(SQLException ex){
        	}
        } 
        return tiposCursos;
	}
	
	public List<CursosVistaReducida> recuperaBusqueda(Map<String, String> criterios){
		conn = ConnectionManager.getInstance().checkOut();
        ResultSet result = null;
        List<CursosVistaReducida> cursos = new LinkedList<CursosVistaReducida>();
        PreparedStatement stmt = null;
        try{
        	stmt = this.configurarBusqueda(criterios);
        	result = stmt.executeQuery();
        	while(result.next()){
        		CursosVistaReducida cursoAux = new CursosVistaReducida(result.getString("idCurso"), result.getString("desCurso"), result.getString("especialidad"), result.getString("entidad"));
        		cursos.add(cursoAux);
        	}
        }catch(SQLException ex){
        	System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
        }finally{
        	finalize();
        	try{
        		if(result != null){
        			result.close();
        		}
        		if(stmt!= null){
        			stmt.close();
        		}
        	}catch(SQLException ex){
        	}
        } 
        return cursos;
	}
	
	private PreparedStatement configurarBusqueda(Map<String, String> criterios) throws SQLException{
		//Crear el SQL
		PreparedStatement stmtBusqueda = null;
		String sqlBusqueda = "";
		boolean sinCriterios = true;
		String cadSelect="SELECT DISTINCT c.idCurso, c.descripcion AS desCurso, c.especialidad, c.entidad ";
		String cadFrom="FROM cursos c", cadWhere=" WHERE";
		int contadorCriterios = 0;
		//Inicializar y configurar el statement
		
		if(!criterios.get("tipoCurso").equals("ninguno")){
			if(contadorCriterios>0){
				cadWhere = cadWhere + " AND";
			}
			contadorCriterios++;
			cadFrom = cadFrom + ", tiposcursos tc";
			cadWhere = cadWhere + " c.idTipo=(SELECT idTipo FROM tiposcursos tc WHERE tc.descripcion=?)";
			sinCriterios = false;
		}
		if(!criterios.get("requisitosCurso").equals("ninguno")){
			if(contadorCriterios>0){
				cadWhere = cadWhere + " AND";
			}
			contadorCriterios++;
			cadFrom = cadFrom + ", requisitoscursos rc";
			cadWhere = cadWhere + " c.idRequisitos=(SELECT idRequisitos FROM requisitoscursos rc WHERE rc.descripcion=?)";
			sinCriterios = false;
		}
		
		sqlBusqueda = cadSelect + cadFrom;
		if(!sinCriterios){
			sqlBusqueda = sqlBusqueda + cadWhere;
		}
		stmtBusqueda = conn.prepareStatement(sqlBusqueda);
		if(!sinCriterios){
			int contadorStmt = 1;
			if(!criterios.get("tipoCurso").equals("ninguno")){
				stmtBusqueda.setString(contadorStmt, criterios.get("tipoCurso"));
				contadorStmt++;
			}
			if(!criterios.get("requisitosCurso").equals("ninguno")){
				stmtBusqueda.setString(contadorStmt, criterios.get("requisitosCurso"));
				contadorStmt++;
			}
		}
		return stmtBusqueda;
	}
	
	public Curso recuperarCurso(String idCurso){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT c.idCurso, c.descripcion, tc.descripcion AS descTipo, rc.descripcion AS descRequisito, " +
				"c.horas, c.observaciones, c.especialidad, c.entidad " +
				"FROM cursos c, tiposcursos tc, requisitoscursos rc " +
				"WHERE c.idCurso=? AND c.idTipo = tc.idTipo AND c.idRequisitos = rc.idRequisitos";
        ResultSet result = null;
        Curso res = null;
        try{
        	stmt = conn.prepareStatement(sql);
        	stmt.setString(1, idCurso);
        	result = stmt.executeQuery();
        	if (result.next()) {
        		res = new Curso(result.getString("idCurso"), result.getString("descripcion"), result.getInt("horas"), 
        				result.getString("especialidad"), result.getString("entidad"), result.getString("observaciones"),
        				result.getString("descTipo"), result.getString("descRequisito"));
        	}
        }catch(SQLException ex){
        	System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
        }finally{
        	finalize();
        	try{
        		if(result != null){
        			result.close();
        		}
        		if(stmt != null){
        			stmt.close();
        		}
        	}catch(SQLException ex){
        	}
        } 
        return res;
	}
	
	public List<GrupoVistaReducida> recuperaGruposCurso(String idCurso){
		conn = ConnectionManager.getInstance().checkOut();
        ResultSet result = null;
        List<GrupoVistaReducida> grupos = new LinkedList<GrupoVistaReducida>();
        PreparedStatement stmt = null;
        String sql = "SELECT g.idGrupo, g.idCurso, g.numeroGrupo, g.numeroAlumnosMax, g.numeroAlumnosActuales, " +
        		"sg.descripcion as situacion FROM grupos g, situacionesgrupos sg WHERE g.idCurso = ? AND g.idSituacion = sg.idSituacion";
        
        try{
        	stmt = conn.prepareStatement(sql);
        	stmt.setString(1, idCurso);
        	result = stmt.executeQuery();
        	while(result.next()){
        		GrupoVistaReducida grupoAux = new GrupoVistaReducida(result.getInt("idGrupo"), result.getString("idCurso"),
        				result.getString("numeroGrupo"), result.getInt("numeroAlumnosMax"), result.getInt("numeroAlumnosActuales"),
        				result.getString("situacion"));
        		grupos.add(grupoAux);
        		
        		
        	}    	
        	}catch(SQLException ex){
            	System.out.println("Message: " + ex.getMessage());
    			System.out.println("SQLState: " + ex.getSQLState());
    			System.out.println("ErrorCode: " + ex.getErrorCode());
            }finally{
            	finalize();
            	try{
            		if(result != null){
            			result.close();
            		}
            		if(stmt != null){
            			stmt.close();
            		}
            	}catch(SQLException ex){
            	}
            }
        
        return grupos;
	}
	
	public boolean modificaCurso(Curso cursoModificado){
		boolean res = true;
		conn = ConnectionManager.getInstance().checkOut();
        ResultSet result = null;
        PreparedStatement stmt = null;
        String sql = "UPDATE cursos SET descripcion=?,horas=?,especialidad=?,entidad=?,observaciones=?," +
        		"idTipo=(SELECT idTipo FROM tiposcursos WHERE descripcion = ?)," +
        		"idRequisitos=(SELECT idRequisitos FROM requisitoscursos WHERE descripcion = ?) " +
        		"WHERE  idCurso=?";
        try{
        	stmt = conn.prepareStatement(sql);
        	stmt.setString(1, cursoModificado.getDescripcion());
        	stmt.setInt(2, cursoModificado.getHoras());
        	stmt.setString(3, cursoModificado.getEspecialidad());
        	stmt.setString(4, cursoModificado.getEntidad());
        	stmt.setString(5, cursoModificado.getObservaciones());
        	stmt.setString(6, cursoModificado.getTipo()); 
        	stmt.setString(7, cursoModificado.getRequisitos());
        	stmt.setString(8, cursoModificado.getIdCurso());
        	stmt.execute();
        	}catch(SQLException ex){
        		res = true;
            	System.out.println("Message: " + ex.getMessage());
    			System.out.println("SQLState: " + ex.getSQLState());
    			System.out.println("ErrorCode: " + ex.getErrorCode());
            }finally{
            	finalize();
            	try{
            		if(result != null){
            			result.close();
            		}
            		if(stmt != null){
            			stmt.close();
            		}
            	}catch(SQLException ex){
            	}
            }
        return res;
	}
	
	public boolean insertaCurso(Curso cursoNuevo){
		boolean res = true;
		conn = ConnectionManager.getInstance().checkOut();
        ResultSet result = null;
        PreparedStatement stmt = null;
        String sql = "INSERT INTO cursos (idCurso, descripcion, horas, especialidad, entidad, observaciones, idTipo, idRequisitos) " +
        		"VALUES(?,?,?,?,?,?,(SELECT idTipo FROM tiposcursos WHERE descripcion = ?),(SELECT idRequisitos FROM requisitoscursos WHERE descripcion = ?))";
        try{
        	stmt = conn.prepareStatement(sql);
        	stmt.setString(1, cursoNuevo.getIdCurso());
        	stmt.setString(2, cursoNuevo.getDescripcion());
        	stmt.setInt(3, cursoNuevo.getHoras());
        	stmt.setString(4, cursoNuevo.getEspecialidad());
        	stmt.setString(5, cursoNuevo.getEntidad());
        	stmt.setString(6, cursoNuevo.getObservaciones());
        	stmt.setString(7, cursoNuevo.getTipo()); 
        	stmt.setString(8, cursoNuevo.getRequisitos());
        	stmt.execute();
        	
        	String sql2 = "INSERT INTO grupos (idCurso, numeroGrupo, idSituacion, idModalidad, idPlataforma, numeroAlumnosMax, " +
        			"numeroAlumnosActuales, aula, fechaInicio, fechaFin, horasPredefinidas, diasLectivos, observaciones) " +
        			"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
    		try{
    			stmt = conn.prepareStatement(sql2);
    			stmt.setString(1, cursoNuevo.getIdCurso());
    			stmt.setString(2, "00");
    			stmt.setInt(3, 1);
    			stmt.setInt(4, 1);
    			stmt.setInt(5, 1);
    			stmt.setInt(6, 1);
    			stmt.setInt(7, 1);
    			stmt.setString(8, "ninguna");
    			stmt.setDate(9, new Date(0000-00-00));
    			stmt.setDate(10, new Date(0000-00-00));
    			stmt.setInt(11, 1);
    			stmt.setInt(12, 1);
    			stmt.setString(13, "");
    			stmt.execute();
	    		}catch(SQLException ex){
	    			res = false;
	    			String sql3 = "DELETE FROM cursos WHERE idCurso =?";
	    	        try{
	    	        	stmt = conn.prepareStatement(sql3);
	    	        	stmt.setString(1, cursoNuevo.getIdCurso());
	    	        	stmt.execute();
	    	        	}catch(SQLException ex2){
	    	        		res = true;
	    	            	System.out.println("Message: " + ex2.getMessage());
	    	    			System.out.println("SQLState: " + ex2.getSQLState());
	    	    			System.out.println("ErrorCode: " + ex2.getErrorCode());
	    	            }
	            	System.out.println("Message: " + ex.getMessage());
	    			System.out.println("SQLState: " + ex.getSQLState());
	    			System.out.println("ErrorCode: " + ex.getErrorCode());
	            }
        	}catch(SQLException ex){
        		res = false;
            	System.out.println("Message: " + ex.getMessage());
    			System.out.println("SQLState: " + ex.getSQLState());
    			System.out.println("ErrorCode: " + ex.getErrorCode());
            }finally{
            	finalize();
            	try{
            		if(result != null){
            			result.close();
            		}
            		if(stmt != null){
            			stmt.close();
            		}
            	}catch(SQLException ex){
            	}
            }
        return res;
	}
	
	public boolean borrarCurso(String idCurso){
		boolean res = true;
		conn = ConnectionManager.getInstance().checkOut();
        ResultSet result = null;
        PreparedStatement stmt = null;
        String sql = "DELETE FROM cursos WHERE idCurso =?";
        try{
        	stmt = conn.prepareStatement(sql);
        	stmt.setString(1, idCurso);
        	stmt.execute();
        	}catch(SQLException ex){
        		res = true;
            	System.out.println("Message: " + ex.getMessage());
    			System.out.println("SQLState: " + ex.getSQLState());
    			System.out.println("ErrorCode: " + ex.getErrorCode());
            }finally{
            	finalize();
            	try{
            		if(result != null){
            			result.close();
            		}
            		if(stmt != null){
            			stmt.close();
            		}
            	}catch(SQLException ex){
            	}
            }
        return res;
	}
	
	public boolean borrarGrupo(Integer idGrupo){
		boolean res = true;
		conn = ConnectionManager.getInstance().checkOut();
        ResultSet result = null;
        PreparedStatement stmt = null;
        String sql = "DELETE FROM grupos WHERE idGrupo =?";
        try{
        	stmt = conn.prepareStatement(sql);
        	stmt.setInt(1, idGrupo);
        	stmt.execute();
        	}catch(SQLException ex){
        		res = true;
            	System.out.println("Message: " + ex.getMessage());
    			System.out.println("SQLState: " + ex.getSQLState());
    			System.out.println("ErrorCode: " + ex.getErrorCode());
            }finally{
            	finalize();
            	try{
            		if(result != null){
            			result.close();
            		}
            		if(stmt != null){
            			stmt.close();
            		}
            	}catch(SQLException ex){
            	}
            }
        return res;
	}
}
